package cargaBaseDatos.mundo;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;

import org.h2.jdbcx.JdbcConnectionPool;

public class CargarYoutube 
{
	private String path;
	
	public CargarYoutube( String path )
	{
		this.path = path;
	}
	
	public void cargarDatosIniciales( )
	{
		System.out.println("Cargando los datos iniciales");
		File directorio = new File( path + "YouTubeBI\\data\\bd\\" );
		
		for( File f : directorio.listFiles() )
		{
			try {
				File destino = new File( "./data/YouTube/iniciales/" + f.getName() );
				if( destino.exists() )
					destino.delete();
				
				FileReader in = new FileReader( f );
			    FileWriter out = new FileWriter( destino );
			    int c;

			    while ((c = in.read()) != -1)
			      out.write(c);

			    in.close();
			    out.close();
			}
			catch( Exception e )
			{
				System.out.println("Error copiando archivo "+f.getName()+": "+e.getMessage());
			}
		}
	}
	
	public void pulirDatos( )
	{
		System.out.println("Puliendo los datos");
		File directorio = new File( "./data/YouTube/iniciales" );
		
		Hashtable<String,String> ids = new Hashtable<String,String>( );
		ids.put("Portal 2: Perfect 10", "4iFDPEO6vYA");
		ids.put("Portal 2 Launch TV Ad", "H50wW4eAFKo");
		ids.put("Aperture Investment Opportunity #4: \"Boots\"", "wX9Sc88qreg");
		ids.put("Aperture Investment Opportunity #3: \"Turrets\"", "6i-nMWgBUp0");
		ids.put("Aperture Investment Opportunity #2: \"Bot Trust\"", "AZMSAzZ76EU");
		ids.put("Aperture Investment Opportunity #1: \"Panels\"", "0qcED35LL8I");
		ids.put("Portal 2 TV Spot", "APAcU3YBhYc");
		ids.put("Excursion Funnel", "1oVpE40V9rE");
		ids.put("Aerial Faith Plate", "fE3B9uGnVlA");
		ids.put("Wheatley Update", "Mz0PvYiHjwE");
		ids.put("Alien Swarm", "603izhzohos");
		ids.put("Propulsion Gel", "Pcf99_DZZew");
		ids.put("Repulsion Gel", "a7V0HBwHfEw");
		ids.put("Pneumatic Diversity Vent", "DOH6BprN9FI");
		ids.put("Thermal Discouragement Beam", "yFRbGppLaUI");
		ids.put("Valentine's Day Gift Buying", "w5I3VnCHWsU");
		ids.put("Need a Sentry Here!", "j1kccz1qziE");
		ids.put("\"The Sacrifice\" Teaser", "SvzjTbBCZTI");
		ids.put("Portal 2 - Full Co-op Trailer", "A88YiZdXugA");
		ids.put("Repulsion Gel #2", "IaZ75vU112Q");
		ids.put("Aerial Faith Plate #2", "laxPhiDqTJA");
		ids.put("Portal 2 Co-op Intro", "JYsfhxnX-Zg");
		ids.put("Portal 2 TV Spot", "APAcU3YBhYc");
		
		PrintWriter out;
		try {
			File destino = new File("./data/YouTube/pulidos/pulidos.txt");
			if( destino.exists() )
				destino.delete();
			out = new PrintWriter( destino );
			for( File f : directorio.listFiles() )
			{
				try {
					BufferedReader lector = new BufferedReader( new FileReader( f ) );
					String linea = lector.readLine();
					
					while( linea != null )
					{
						String[] campos = linea.split(",");
						
						String id = ids.get(campos[0]);
						String views = campos[1];
						String favs = campos[2];
						
						String fecha = f.getName().substring(0,f.getName().length()-4).replace("_", "/");
						
						out.println( id + "," + views + "," + favs + "," + fecha );
						
						linea = lector.readLine();
					}
					
				}
				catch( Exception e ) {
					e.printStackTrace();
				}
			}
			out.close();
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}
	}
	
	public void cargarABD( ) throws Exception
	{
		System.out.println("Cargando en base de datos");
		JdbcConnectionPool cp = JdbcConnectionPool.create( "jdbc:h2:~/test", "sa", "sa" );
		File fuente = new File("./data/YouTube/pulidos/pulidos.txt");
		Connection conn = cp.getConnection();
		BufferedReader lector = new BufferedReader( new FileReader( fuente ) );
		String linea = lector.readLine();
		
		int i = 1;
		while( linea != null )
		{
			String[] sa = linea.split(",");
			Statement p = conn.createStatement();
			try {
				p.execute( "INSERT INTO dbo.DimYouTubeCount VALUES("+i+",'"+sa[0]+"',"+sa[1]+","+sa[2]+",'"+ sa[3].replace("/", "-")+"')" );
			} catch (SQLException e) {
				//Sucede si ya metio los datos
			}
            i++;
            linea = lector.readLine();
		}
		conn.close();
	    cp.dispose();
	}
}
